# 数据库查询优化工具类
from django.db.models import Prefetch, Q, Count, Sum, Avg
from django.core.cache import cache
from django.utils import timezone
from datetime import timedelta
import logging

logger = logging.getLogger(__name__)


class QueryOptimizer:
    """数据库查询优化工具类"""
    
    @staticmethod
    def get_optimized_assets(filters=None, select_related_fields=None, prefetch_fields=None):
        """优化的资产查询"""
        from ..models import Asset
        
        # 基础查询优化
        queryset = Asset.objects.all()
        
        # 默认关联查询优化
        default_select_related = ['category', 'department', 'user', 'location', 'supplier', 'contract']
        if select_related_fields:
            default_select_related.extend(select_related_fields)
        
        queryset = queryset.select_related(*default_select_related)
        
        # 预取相关数据
        default_prefetch = ['asset_images', 'asset_files']
        if prefetch_fields:
            default_prefetch.extend(prefetch_fields)
        
        queryset = queryset.prefetch_related(*default_prefetch)
        
        # 应用筛选条件
        if filters:
            if 'status' in filters and filters['status']:
                queryset = queryset.filter(status=filters['status'])
            if 'category_id' in filters and filters['category_id']:
                queryset = queryset.filter(category_id=filters['category_id'])
            if 'department_id' in filters and filters['department_id']:
                queryset = queryset.filter(department_id=filters['department_id'])
            if 'search' in filters and filters['search']:
                search_term = filters['search']
                queryset = queryset.filter(
                    Q(name__icontains=search_term) |
                    Q(asset_number__icontains=search_term) |
                    Q(description__icontains=search_term)
                )
        
        return queryset.order_by('-created_at')
    
    @staticmethod
    def get_optimized_asset_logs(asset_id=None, limit=50):
        """优化的资产日志查询"""
        from ..models import AssetLog
        
        queryset = AssetLog.objects.select_related('asset', 'operator')
        
        if asset_id:
            queryset = queryset.filter(asset_id=asset_id)
        
        return queryset.order_by('-created_at')[:limit]
    
    @staticmethod
    def get_optimized_borrow_requests(user=None, status=None):
        """优化的借用申请查询"""
        from ..models import AssetBorrow
        
        queryset = AssetBorrow.objects.select_related(
            'asset', 'asset__category', 'borrower', 'approver', 'borrowed_by', 'returned_by'
        )
        
        if user:
            queryset = queryset.filter(borrower=user)
        
        if status:
            queryset = queryset.filter(status=status)
        
        return queryset.order_by('-created_at')
    
    @staticmethod
    def get_optimized_maintenance_records(asset_id=None, status=None):
        """优化的维修记录查询"""
        from ..models import MaintenanceRecord
        
        queryset = MaintenanceRecord.objects.select_related(
            'asset', 'asset__category', 'reported_by', 'assigned_to', 'supplier'
        )
        
        if asset_id:
            queryset = queryset.filter(asset_id=asset_id)
        
        if status:
            queryset = queryset.filter(status=status)
        
        return queryset.order_by('-created_at')
    
    @staticmethod
    def get_optimized_workflow_instances(template_id=None, status=None, date_range=7):
        """优化的工作流实例查询"""
        from ..models import WorkflowInstance
        
        # 时间范围过滤
        end_date = timezone.now()
        start_date = end_date - timedelta(days=date_range)
        
        queryset = WorkflowInstance.objects.select_related(
            'template', 'created_by'
        ).filter(created_at__range=[start_date, end_date])
        
        if template_id:
            queryset = queryset.filter(template_id=template_id)
        
        if status:
            queryset = queryset.filter(status=status)
        
        return queryset.order_by('-created_at')
    
    @staticmethod
    def get_optimized_workflow_tasks(assignee=None, status='pending'):
        """优化的工作流任务查询"""
        from ..models import WorkflowStepTask
        
        queryset = WorkflowStepTask.objects.select_related(
            'workflow_instance', 'workflow_instance__template', 
            'workflow_instance__created_by', 'assignee'
        )
        
        if assignee:
            queryset = queryset.filter(assignee=assignee)
        
        if status:
            queryset = queryset.filter(status=status)
        
        return queryset.order_by('created_at')
    
    @staticmethod
    def get_dashboard_statistics(use_cache=True):
        """获取仪表板统计数据（带缓存）"""
        cache_key = 'dashboard_stats'
        
        if use_cache:
            cached_stats = cache.get(cache_key)
            if cached_stats:
                return cached_stats
        
        from ..models import Asset, AssetBorrow, MaintenanceRecord, InventoryAlert
        
        # 使用聚合查询优化统计
        asset_stats = Asset.objects.aggregate(
            total_count=Count('id'),
            in_use_count=Count('id', filter=Q(status='in_use')),
            maintenance_count=Count('id', filter=Q(status='maintenance')),
            idle_count=Count('id', filter=Q(status='idle'))
        )
        
        borrow_stats = AssetBorrow.objects.aggregate(
            pending_count=Count('id', filter=Q(status='pending')),
            borrowed_count=Count('id', filter=Q(status='borrowed')),
            overdue_count=Count('id', filter=Q(
                status='borrowed',
                expected_return_date__lt=timezone.now().date()
            ))
        )
        
        maintenance_stats = MaintenanceRecord.objects.aggregate(
            pending_count=Count('id', filter=Q(status='pending')),
            in_progress_count=Count('id', filter=Q(status='in_progress')),
            total_cost=Sum('cost')
        )
        
        alert_stats = InventoryAlert.objects.aggregate(
            active_count=Count('id', filter=Q(status='active'))
        )
        
        stats = {
            'assets': asset_stats,
            'borrows': borrow_stats,
            'maintenance': maintenance_stats,
            'alerts': alert_stats,
            'updated_at': timezone.now()
        }
        
        # 缓存5分钟
        if use_cache:
            cache.set(cache_key, stats, 300)
        
        return stats
    
    @staticmethod
    def get_asset_analytics_data(filters=None, use_cache=True):
        """获取资产分析数据（带缓存）"""
        cache_key = f'asset_analytics_{hash(str(filters))}'
        
        if use_cache:
            cached_data = cache.get(cache_key)
            if cached_data:
                return cached_data
        
        from ..models import Asset
        
        queryset = Asset.objects.all()
        
        # 应用筛选条件
        if filters:
            if filters.get('category_id'):
                queryset = queryset.filter(category_id=filters['category_id'])
            if filters.get('department_id'):
                queryset = queryset.filter(department_id=filters['department_id'])
            if filters.get('date_range'):
                days = int(filters['date_range'])
                start_date = timezone.now().date() - timedelta(days=days)
                queryset = queryset.filter(purchase_date__gte=start_date)
        
        # 按类别统计
        category_stats = queryset.values('category__name').annotate(
            count=Count('id'),
            total_value=Sum('purchase_price')
        ).order_by('-count')
        
        # 按状态统计
        status_stats = queryset.values('status').annotate(
            count=Count('id')
        ).order_by('-count')
        
        # 按部门统计
        department_stats = queryset.values('department__name').annotate(
            count=Count('id'),
            total_value=Sum('purchase_price')
        ).order_by('-count')
        
        data = {
            'category_stats': list(category_stats),
            'status_stats': list(status_stats),
            'department_stats': list(department_stats),
            'total_assets': queryset.count(),
            'total_value': queryset.aggregate(Sum('purchase_price'))['purchase_price__sum'] or 0,
            'updated_at': timezone.now()
        }
        
        # 缓存10分钟
        if use_cache:
            cache.set(cache_key, data, 600)
        
        return data
    
    @staticmethod
    def bulk_update_asset_status(asset_ids, new_status, operator):
        """批量更新资产状态（优化版本）"""
        from ..models import Asset, AssetLog
        
        # 批量更新资产状态
        updated_count = Asset.objects.filter(id__in=asset_ids).update(
            status=new_status,
            updated_at=timezone.now()
        )
        
        # 批量创建日志记录
        log_entries = []
        for asset_id in asset_ids:
            log_entries.append(AssetLog(
                asset_id=asset_id,
                action='status_change',
                description=f'状态变更为: {new_status}',
                operator=operator,
                created_at=timezone.now()
            ))
        
        AssetLog.objects.bulk_create(log_entries)
        
        return updated_count
    
    @staticmethod
    def get_overdue_items(days_ahead=7):
        """获取即将逾期的项目（优化查询）"""
        from ..models import AssetBorrow, MaintenanceRecord, Contract
        
        future_date = timezone.now().date() + timedelta(days=days_ahead)
        
        # 即将逾期的借用
        overdue_borrows = AssetBorrow.objects.select_related(
            'asset', 'borrower'
        ).filter(
            status='borrowed',
            expected_return_date__lte=future_date
        ).order_by('expected_return_date')
        
        # 即将逾期的维修
        overdue_maintenance = MaintenanceRecord.objects.select_related(
            'asset', 'assigned_to'
        ).filter(
            status__in=['pending', 'in_progress'],
            expected_completion_date__lte=future_date
        ).order_by('expected_completion_date')
        
        # 即将到期的合同
        expiring_contracts = Contract.objects.select_related(
            'supplier'
        ).filter(
            status='active',
            end_date__lte=future_date
        ).order_by('end_date')
        
        return {
            'overdue_borrows': overdue_borrows,
            'overdue_maintenance': overdue_maintenance,
            'expiring_contracts': expiring_contracts
        }
    
    @staticmethod
    def clear_related_cache(model_name, instance_id=None):
        """清除相关缓存"""
        cache_patterns = [
            'dashboard_stats',
            f'asset_analytics_*',
            f'{model_name}_*'
        ]
        
        if instance_id:
            cache_patterns.append(f'{model_name}_{instance_id}_*')
        
        # 这里可以实现更复杂的缓存清理逻辑
        for pattern in cache_patterns:
            try:
                cache.delete(pattern)
            except Exception as e:
                logger.warning(f"清除缓存失败: {pattern}, 错误: {e}")
